package helper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import myJDBC.myConnection;
import myJDBC.lowLevel.References;

import constants.AttributeConstants;

public class Helper {	

	
	public static void CheckRouterInPorch(int taskId) throws SQLException {	
		int porch = GetPorch(taskId);
		PreparedStatement statement = null;
		int router = -1;
		if (myConnection.dbConnection != null) {
			try {
				String selectRouter = 	"select \"object_id\" from \"objects\" where \"parent_id\"=? and \"object_type_id\"=54 ";//54="router"
				statement = myConnection.dbConnection.prepareStatement(selectRouter);
				statement.setInt(1, porch);
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					router = rs.getInt(1);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}finally {
				if (statement != null)
					statement.close();
			}
		}
		ArrayList<Integer> next_task = new ArrayList<Integer>();
		try{
			if(router==-1){//there isn't router in the porch
				next_task = References.returnIdObj(AttributeConstants.ref_next_on_no, taskId);
				String updateParam =  
						"UPDATE \"params\" SET \"list_value_id\" = 31 WHERE \"object_id\"=?  and \"list_value_id\" = 30";
				statement = myConnection.dbConnection.prepareStatement(updateParam);
				statement.setInt(1, next_task.get(0));
				statement.executeUpdate();		
			}else{//there is router in the porch
				next_task = References.returnIdObj(AttributeConstants.ref_next_on_no, taskId);
				String updateParam =  
						"UPDATE \"params\" SET \"list_value_id\" = 34 WHERE \"object_id\"=? and \"list_value_id\" = 30 ";
				statement = myConnection.dbConnection.prepareStatement(updateParam);
				statement.setInt(1, next_task.get(0));
				statement.executeUpdate();		
				
				next_task = References.returnIdObj(AttributeConstants.ref_next_on_yes, taskId);		
				//exec next auto-task
				CheckFreePort(next_task.get(0));
			}			
			String updateThisTask =   
					"UPDATE \"params\" SET \"list_value_id\" = 36 WHERE \"object_id\"=?  and \"list_value_id\" = 35";
			statement = myConnection.dbConnection.prepareStatement(updateThisTask);
			statement.setInt(1, taskId);
			statement.executeUpdate();
		}catch (SQLException e) {
			System.out.println(e.getMessage());
		}finally{
			if(statement!=null)
				statement.close();			
		}
	}
	
	public static void CheckFreePort(int taskId) throws SQLException {
		int porch = GetPorch(taskId);
		PreparedStatement statement = null;
		int router = -1;
		if (myConnection.dbConnection != null) {
			try {
				String selectRouter = 	"select \"object_id\" from \"objects\" where \"parent_id\"=? and \"object_type_id\"=54 ";//54="router"
				statement = myConnection.dbConnection.prepareStatement(selectRouter);
				statement.setInt(1, porch);
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					router = rs.getInt(1);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}finally{
				if(statement!=null)
					statement.close();			
			}
		}
		if (myConnection.dbConnection != null) {
			try {
				
				ArrayList<Integer> ports = new ArrayList<Integer>();
				String selectPortsFromRouter = 	
						"select \"object_id\" from \"objects\" where \"parent_id\"=? and \"object_type_id\"=56 and not \"object_id\" in (select \"object_id\" from \"references\" where \"attribute_id\"=53) ";
				ArrayList<Integer> next_task = new ArrayList<Integer>();
				statement = myConnection.dbConnection.prepareStatement(selectPortsFromRouter);
				statement.setInt(1, router);
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					ports.add(rs.getInt(1));
				}
				if(ports.size()>0){//there is port(s) in the router
					next_task = References.returnIdObj(AttributeConstants.ref_next_on_no, taskId);
					String updateParamCansel =  "UPDATE \"params\" SET \"list_value_id\" = 34 WHERE \"object_id\"=?  and \"attribute_id\" = 19";
					statement = myConnection.dbConnection.prepareStatement(updateParamCansel);
					statement.setInt(1, next_task.get(0));
					statement.executeUpdate();
					
					next_task = References.returnIdObj(AttributeConstants.ref_next_on_yes, taskId);
					String updateParamInit =  "UPDATE \"params\" SET \"list_value_id\" = 31 WHERE \"object_id\"=?  and \"attribute_id\" = 19";  //\"list_value_id\" = 30";
					statement = myConnection.dbConnection.prepareStatement(updateParamInit);
					statement.setInt(1, next_task.get(0));
					statement.executeUpdate();								
				}else{		
					ArrayList<Integer> cards = new ArrayList<Integer>();
					String selectCards = 	
							"select \"object_id\" from \"objects\" where \"parent_id\"=? and \"object_type_id\"=55 ";
					next_task = new ArrayList<Integer>();
					statement = myConnection.dbConnection.prepareStatement(selectCards);
					statement.setInt(1, router);
					rs = statement.executeQuery();
					while (rs.next()) {
						cards.add(rs.getInt(1));
					}
					
					ports = new ArrayList<Integer>();
					for(int i=0;i<cards.size();i++){
						String selectPorts = 	
								"select \"object_id\" from \"objects\" where \"parent_id\"=? and \"object_type_id\"=56 and not \"object_id\" in (select \"object_id\" from \"references\" where \"attribute_id\"=53) ";
						next_task = new ArrayList<Integer>();
						statement = myConnection.dbConnection.prepareStatement(selectPorts);
						statement.setInt(1, cards.get(i));
						rs = statement.executeQuery();
						while (rs.next()) {
							ports.add(rs.getInt(1));
						}
					}
					if(ports.size()>0){//there is port(s) in the router
						next_task = References.returnIdObj(AttributeConstants.ref_next_on_no, taskId);
						String updateParamCansel =  "UPDATE \"params\" SET \"list_value_id\" = 34 WHERE \"object_id\"=?  and \"attribute_id\" = 19";
						statement = myConnection.dbConnection.prepareStatement(updateParamCansel);
						statement.setInt(1, next_task.get(0));
						statement.executeUpdate();
						
						next_task = References.returnIdObj(AttributeConstants.ref_next_on_yes, taskId);
						String updateParamInit =  "UPDATE \"params\" SET \"list_value_id\" = 31 WHERE \"object_id\"=?  and \"attribute_id\" = 19";  //\"list_value_id\" = 30";
						statement = myConnection.dbConnection.prepareStatement(updateParamInit);
						statement.setInt(1, next_task.get(0));
						statement.executeUpdate();								
					}else{//there isn't port(s) in the router
						next_task = References.returnIdObj(AttributeConstants.ref_next_on_no, taskId);
						String updateParam =  "UPDATE \"params\" SET \"list_value_id\" = 31 WHERE \"object_id\"=?  and \"attribute_id\" = 19";
						statement = myConnection.dbConnection.prepareStatement(updateParam);
						statement.setInt(1, next_task.get(0));
						statement.executeUpdate();
					}
				}
				String updateThisTask =   "UPDATE \"params\" SET \"list_value_id\" = 36 WHERE \"object_id\"=?  and \"attribute_id\" = 23";
				statement = myConnection.dbConnection.prepareStatement(updateThisTask);
				statement.setInt(1, taskId);
				statement.executeUpdate();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}finally{
				if(statement!=null)
					statement.close();			
			}	
		}				
	}
	
	public static int GetPorch(int taskId) throws SQLException{
		int order=-1;
		ArrayList<Integer> apartment = new ArrayList<Integer>();
		try {
			PreparedStatement statement = myConnection.dbConnection.prepareStatement("select \"parent_id\" from \"objects\" where \"object_id\"=?");
			statement.setInt(1, taskId);
			ResultSet rs = statement.executeQuery();
			if(rs.next())
				order =rs.getInt(1);
			apartment = References.returnIdObj(AttributeConstants.ref_apartment, order);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}		
		int porch=0;
		PreparedStatement statement = null;
		if (myConnection.dbConnection != null) {
			try {
				statement = myConnection.dbConnection.prepareStatement("select \"parent_id\" from \"objects\" where \"object_id\"=?");
				statement.setInt(1, apartment.get(0));
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					porch = rs.getInt(1);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}finally {
				if (statement != null)
					statement.close();
			}
		}
		return porch;
		
	}
}

	
	

